package beans;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import util.ConnectionManager;
import util.SystemException;
import util.Utilities;

/**
 *
 * @author HOME
 */
public class DeliverBean {

    private int deliverID;
    private int paymentID;
    private String deliverMethod;
    private String clientName;
    private String address;
    private String status;
    private String comment;
    private String bookdate;
    private String booktime;

    /** Creates a new instance of DeliverBean */
    public DeliverBean() {
    }

    public DeliverBean(int deliverID, int paymentID, String deliverMethod, String clientName, String address,
            String status, String comment, String bookdate, String booktime) {
        this.deliverID = deliverID;
        this.paymentID = paymentID;
        this.deliverMethod = deliverMethod;
        this.clientName = clientName;
        this.address = address;
        this.status = status;
        this.comment = comment;
        this.bookdate = bookdate;
        this.booktime = booktime;
    }

    public static DeliverBean oneSetOfResultSetToItem(final ResultSet rs) throws SQLException {
        int deliverID = rs.getInt("deliverID");
        int paymentID = rs.getInt("paymentID");
        String deliverMethod = rs.getString("deliverMethod");
        String clientName = rs.getString("clientName");
        String address = rs.getString("address");
        String status = rs.getString("status");
        String comment = rs.getString("comment");
        String bookdate = rs.getString("bookdate");
        String booktime = rs.getString("booktime");

        DeliverBean deliver = new DeliverBean(deliverID, paymentID, deliverMethod, clientName, address, status, comment,
                bookdate, booktime);
        int i =1;
        return deliver;
    }

    public static void updateDeliver(String status, String comment, String deliverID, final PrintWriter out) {

        Connection con = null;
        Statement stmt = null;
        int rs = 0;
        ModelBean result = null;
        try {
            con = ConnectionManager.connect();

            String query = ("UPDATE deliver SET status ='" + status + "', comment ='" + comment + "' WHERE deliverID ='" + deliverID + "';");
            stmt = con.createStatement();
            rs = stmt.executeUpdate(query);
            con.commit();
        //result = oneSetOfResultSetToItem(rs);

        } catch (SQLException e) {
            out.println(e);

        } finally {
            Utilities.close(stmt, con);
        }
    }

    public static DeliverBean getDeliverBean(String deliverID) throws SystemException {
        ResultSet rs = null;
        Statement stmt = null;
        Connection con = null;
        DeliverBean product = null;
        try {
            con = ConnectionManager.connect();
            String query = "select * from deliver where deliverID=" + deliverID;
            stmt = con.createStatement();
            rs = stmt.executeQuery(query);
            while (rs.next()) {
                product = oneSetOfResultSetToItem(rs);
            }
        } catch (Exception e) {
            throw new SystemException("Database Service is not availiable for a moment.");
        } finally {
            Utilities.close(rs, stmt, con);
        }
        return product;
    }

    public static int getTotalRecordCount() throws SystemException {
        ResultSet rs = null;
        Statement stmt = null;
        Connection con = null;

        try {
            con = ConnectionManager.connect();
            String query = "select count(deliverID) as no from deliver";
            stmt = con.createStatement();
            rs = stmt.executeQuery(query);

            while (rs.next()) {
                int count = rs.getInt("no");
                return count;
            }

        } catch (Exception e) {
            throw new SystemException("Database Service is not availiable for a moment.");
        } finally {
            Utilities.close(rs, stmt, con);
        }
        return 0;
    }

    public static DeliverBean[] getRecords() throws SystemException {
        ResultSet rs = null;
        Statement stmt = null;
        Connection con = null;
        DeliverBean[] finalResult = new DeliverBean[0];
        try {
            con = ConnectionManager.connect();


            String query = "select * from deliver order by deliverID";
            stmt = con.createStatement();
            rs = stmt.executeQuery(query);
            ArrayList<DeliverBean> result = new ArrayList<DeliverBean>();
            while (rs.next()) {
                DeliverBean product = oneSetOfResultSetToItem(rs);
                result.add(product);
            }
            finalResult = (DeliverBean[]) result.toArray(finalResult);
        } catch (Exception e) {
            e.printStackTrace();
            throw new SystemException("Database Service is not availiable for a moment.");

        } finally {
            Utilities.close(rs, stmt, con);
        }
        return finalResult;
    }

    public void addItem(String itemID, int quantity) {
        ResultSet rs = null;
        Statement stmt = null;
        Connection con = null;
        try {
            con = ConnectionManager.connect();
            String query = String.format("insert into deliver_advance set deliverID=%s, itemID=%s, quantity=%s", getDeliverID(), itemID, quantity);
            stmt = con.createStatement();
            stmt.executeUpdate(query);
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            Utilities.close(rs, stmt, con);
        }

    }
    /*
    public ItemInformation[]  getItems() throws SystemException  {
    ResultSet rs = null;
    Statement stmt = null;
    Connection con = null;
    ItemInformation[] finalResult = new ItemInformation[0];
    try {
    con = ConnectionManager.connect();
    String query = "select * from deliver_advance where deliverID="+getDeliverID();
    stmt = con.createStatement();
    rs = stmt.executeQuery(query);
    ArrayList<ItemInformation> result = new ArrayList<ItemInformation>();
    while(rs.next()) {
    int deliverID = rs.getInt("deliverID");
    int itemID = rs.getInt("itemID");
    int quantity = rs.getInt("quantity");
    ItemInformation info = new ItemInformation(deliverID, itemID, quantity);
    result.add(info);
    }
    finalResult = (ItemInformation[]) result.toArray(finalResult);
    }catch (Exception e) {
    throw new SystemException("Database Service is not availiable for a moment.");
    }finally {
    Utilities.close(rs, stmt, con);
    }
    return finalResult;
    } */

    static DeliverBean[] deliver(final String query) throws SystemException {
        ResultSet rs = null;
        Statement stmt = null;
        Connection con = null;
        DeliverBean[] finalResult = new DeliverBean[0];
        try {
            con = ConnectionManager.connect();

            stmt = con.createStatement();
            rs = stmt.executeQuery(query);
            ArrayList<DeliverBean> result = new ArrayList<DeliverBean>();
            while (rs.next()) {
                DeliverBean product = oneSetOfResultSetToItem(rs);
                result.add(product);
            }
            finalResult = (DeliverBean[]) result.toArray(finalResult);
        } catch (Exception e) {
            throw new SystemException("Database Service is not availiable for a moment.");
        } finally {
            Utilities.close(rs, stmt, con);
        }
        return finalResult;
    }

    public static ModelBean[] getItems(final String deliverID) throws SystemException {
        return ModelBean.model(
                "SELECT model.itemID AS itemID, model.name AS name, model.type AS type, model.company AS company," +
                "model.scale AS scale, model.price AS price, model.description AS description, model.imageURL AS imageURL, " +
                "deliver_advance.quantity AS quantity3 " +
                "FROM model, deliver_advance" +
                "WHERE " + deliverID + " = deliver_advance.deliverID AND deliver_advance.itemID = model.itemID");
    }

    public int getDeliverID() {
        return deliverID;
    }

    public int getPaymentID() {
        return paymentID;
    }

    public String getDeliverMethod() {
        return deliverMethod;
    }

    public String getClientName() {
        return clientName;
    }

    public String getAddress() {
        return address;
    }

    public String getStatus() {
        return status;
    }

    public String getComment() {
        return comment;
    }

    public String getBookdate() {
        return bookdate;
    }

    public String getBooktime() {
        return booktime;
    }
}
